package myJDBC.lowLevel;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.OracleTypes;
import myJDBC.*;

public class Attributes {
	// attr_id, attr_name, attr_type_name
	public static ArrayList<String> returnAttributes() {
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select t1.\"attribute_id\", t1.\"name\", t2.\"name\"")
				           .append("from \"attributes\" t1, \"attr_type\" t2 ")
				           .append("where t1.\"attr_type_id\"=t2.\"attr_type_id\"");
		if (myConnection.dbConnection != null) {
			try {
				Statement statement = myConnection.dbConnection
						.createStatement();
				ResultSet rs = statement.executeQuery(selectAttributes.toString());
				String tmp;
				while (rs.next()) {
					tmp = rs.getString(1);
					al.add(tmp);
					tmp = rs.getString(2);
					al.add(tmp);
					tmp = rs.getString(3);
					al.add(tmp);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;
	}

	// attr_id, attr_name, attr_type_name
	public static ArrayList<String> returnArrayAttributes() {
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select t1.\"attribute_id\", t1.\"name\", t2.\"name\"")
						   .append("from \"attributes\" t1, \"attr_type\" t2 ")
						   .append("where t1.\"attr_type_id\"=t2.\"attr_type_id\" and t2.\"name\"=\'array\' ");
		if (myConnection.dbConnection != null) {
			try {
				Statement statement = myConnection.dbConnection
						.createStatement();
				ResultSet rs = statement.executeQuery(selectAttributes.toString());
				String tmp;
				while (rs.next()) {
					tmp = rs.getString(1);
					al.add(tmp);
					tmp = rs.getString(2);
					al.add(tmp);
					tmp = rs.getString(3);
					al.add(tmp);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;
	}

	// attr_id, attr_name, attr_type_name
	public static ArrayList<String> returnAttribute(int attrId) {
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select t1.\"attribute_id\", t1.\"name\", t2.\"name\"")
				   		   .append("from \"attributes\" t1, \"attr_type\" t2 ")
				   		   .append("where t1.\"attribute_id\"=? ")
				   		   .append(" and t1.\"attr_type_id\"=t2.\"attr_type_id\"");
		if (myConnection.dbConnection != null) {
	 	 try (PreparedStatement pstmt = myConnection.dbConnection
		                .prepareStatement(selectAttributes.toString())) {
				   pstmt.setInt(1, attrId);
				   pstmt.executeUpdate(); 
				   ResultSet rs = pstmt.executeQuery();
				String tmp;
				while (rs.next()) {
					tmp = rs.getString(1);
					al.add(tmp);
					tmp = rs.getString(2);
					al.add(tmp);
					tmp = rs.getString(3);
					al.add(tmp);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;
	}

	
	public static int createAttribute(String name, int attrTypeId)
			throws SQLException {
		int i = -1;
		CallableStatement cs = null;
		try {
			StringBuilder plsql = new StringBuilder();
			              plsql.append("begin ")
					           .append("insert into \"attributes\"(\"name\", \"attr_type_id\") values (?,?) ")
					           .append("RETURNING \"attribute_id\" ").append("INTO ?; ").append("END;");
			cs = myConnection.dbConnection.prepareCall(plsql.toString());
			cs.setString(1, name);
			cs.setInt(2, attrTypeId);
			cs.registerOutParameter(3, OracleTypes.NUMBER);
			cs.execute();
			i = cs.getInt(3);
		} finally {
			if (cs != null)
				cs.close();
		}
		return i;
	}

	public static ArrayList<String> returnAttribute(String attrName) {
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select t1.\"attribute_id\", t1.\"name\", t2.\"name\"")
				           .append("from \"attributes\" t1, \"attr_type\" t2 ")
				           .append("where t1.\"name\"=? and t1.\"attr_type_id\"=t2.\"attr_type_id\"");
		if (myConnection.dbConnection != null) {
		 	 try (PreparedStatement pstmt = myConnection.dbConnection
		                .prepareStatement(selectAttributes.toString())) {
				   pstmt.setString(1, attrName);
				   pstmt.executeUpdate(); 
				   ResultSet rs = pstmt.executeQuery();
				String tmp;
				while (rs.next()) {
					tmp = rs.getString(1);
					al.add(tmp);
					tmp = rs.getString(2);
					al.add(tmp);
					tmp = rs.getString(3);
					al.add(tmp);
				}
			} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
			return al;
		} else
			return null;		
	}

}
